package com.ibm.iisp.common.util;

import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 用来模拟实现命名参数功能<br>
 * 如：select * from people where first_name =:name and address =:address<br>
 * 变成<br>
 * select * from people where first_name = ? address = ?
 */
public class NamedParamSqlUtil {
//	public static void main(String[] s) {
//		NamedParamSqlUtil u = new NamedParamSqlUtil();
//		String sql = u.parseSql("select * from people where first_name =:name and address =:address");
//		System.out.println(sql);
//		System.out.println(u.getParamsMap());
//	}

	static final Logger log = LoggerFactory.getLogger(NamedParamSqlUtil.class);
	private Map<Integer, String> paramsMap = new HashMap<Integer, String>();

	public Map<Integer, String> getParamsMap() {
		return paramsMap;
	}

	public void emptyMap() {
		paramsMap.clear();
	}


	/**
	 * 分析处理带命名参数的SQL语句。使用Map存储参数，然后将参数替换成? <br>
	 * 
	 * @param sql 带参数名的SQL
	 * @return 没有参数名的SQL
	 */
	public String parseSql(String sql) {
		String regex = "(:(\\w+))";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sql);
		emptyMap();
		int idx = 1;
		while (m.find()) {
			// 参数名称可能有重复，使用序号来做Key
			paramsMap.put(idx++, m.group(2));
		}
		String result = sql.replaceAll(regex, "?");
		log.trace("分析前：{}", sql);
		log.debug("分析后：{}", result);
		return result;
	}

	/**
	 * 使用参数值Map，填充pStat <br>
	 * 
	 * @param pStat PreparedStatement
	 * @param pMap  命名参数的值表，其中的值可以比较所需的参数多。
	 * @return 是否成功
	 */
	public boolean fillParameters(PreparedStatement pStat, Map<String, Object> pMap) {
		boolean result = true;
		String paramName = null;
		Object paramValue = null;
		int idx = 1;
		for (Entry<Integer, String> entry : paramsMap.entrySet()) {
			paramName = entry.getValue();
			idx = entry.getKey().intValue();
			// 不包含会返回null
			paramValue = pMap.get(paramName);
			try {
				pStat.setObject(idx, paramValue);
			} catch (Exception e) {
				log.error("填充参数出错 {}" + e.getMessage(), e);
				result = false;
			}
		}
		return result;
	}
}
